﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using JYB.SummitExam.Model;

namespace JYB.SummitExam.Common
{
    public class Common
    {
        public static string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

        #region 实现登录的方法
        public static T Login<T>(string name, string pwd)
        {
            string SqlText = "select {0} from [{1}] where Name=@name and Password=@pwd";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            SqlText = string.Format(SqlText, SelectField, type.Name);
            var oObject = (T)Activator.CreateInstance(type);
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(SqlText, con);
                com.Parameters.Add(new SqlParameter("@name", name));
                com.Parameters.Add(new SqlParameter("@pwd", pwd));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    foreach (var pop in type.GetProperties())
                    {
                        object oValue = reader[pop.Name];
                        if (oValue is DBNull)
                        {
                            pop.SetValue(oObject, null);
                        }
                        else
                        {
                            pop.SetValue(oObject, oValue);
                        }
                    }
                }
            }
            return oObject;
        }

        #endregion

        #region 获取列表的方法
        public static List<T> GetList<T>()
        {
            string sql = "select {0} from [{1}]";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<T> list = new List<T>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    var oObject = (T)Activator.CreateInstance(type);
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(oObject, null);
                        }
                        else
                        {
                            item.SetValue(oObject, oValue);
                        }
                    }
                    list.Add(oObject);
                }
            }
            return list;
        }
        #endregion

        #region 根据学院编号获取该学院下的方向列表
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">学院编号</param>
        /// <returns></returns>
        public static List<T> MajorList<T>(int id)
        {
            string sql = "select {0} from [{1}] where College_ID=@id";
            Type type = typeof(T);
            var SelectField = string.Join(",", type.GetProperties().Select(p => string.Format("{0}", p.Name)));
            sql = string.Format(sql, SelectField, type.Name);
            List<T> list = new List<T>();
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.Add(new SqlParameter("@id", id));
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    var oObject = (T)Activator.CreateInstance(type);
                    foreach (var item in type.GetProperties())
                    {
                        object oValue = reader[item.Name];
                        if (oValue is DBNull)
                        {
                            item.SetValue(oObject, null);
                        }
                        else
                        {
                            item.SetValue(oObject, oValue);
                        }
                    }
                    list.Add(oObject);
                }
            }
            return list;
        }
        #endregion

        #region 添加
        public static int ADD<T>(T t)
        {
            string sqyj = "insert into [{0}] ({1}) values ({2})";
            Type type = typeof(T);
            sqyj = string.Format(sqyj,
                type.Name,
                string.Join(",", type.GetProperties().Where(p => p.Name != "ID").Select(x => string.Format("[{0}]", x.Name))),
                string.Join(",", type.GetProperties().Where(p => p.Name != "ID").Select(x => string.Format("@{0}", x.Name)))
                );
            using (SqlConnection con = new SqlConnection(connString))
            {
                con.Open();
                SqlCommand com = new SqlCommand(sqyj, con);
                SqlParameter[] pamter = type.GetProperties().Where(p => !"Id".Equals(p.Name))
                    .Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t) ?? DBNull.Value))
                    .ToArray();
                com.Parameters.AddRange(pamter);
                return com.ExecuteNonQuery();
            }

        }

        #endregion

    }
}
